﻿/*-----------------------------------------------------------------------
 * <copyright file="DataProvider.cs" company="O-RID">
 *     Copyright (c)2010 O-RID Co.,Ltd.
 * </copyright>
 * <author>Zhang Jun</author>
 * <summary>
 *          WCFCommonQuery.svc服务实现的具体方法
 *          Get（Set）+服务方法名
 *          作成日：06/24/2010
 *                  08/27/2010 增加查询SV、SSV 对应的组别信息，或是查询某组由那些SV来管理
 *                  01/10/2011 增加 F和GJOB的枚举值
 *                  04/22/2011 增加 JOB种类 判定A，判定B的枚举值
 *                  04/25/2011 增加 案件名 查询
 *                  04/25/2011 增加 案件名查询信息
 *                  06/17/2011 修改 案件名 查询
 *                  06/29/2011 增加 UserOrder（客户种类）查询条件信息。
 *                  07/04/2011 增加 ScriptType(帐票种类) 查询条件信息。
 *                  07/06/2011 增加 WordType（文字种类）查询条件信息。
 *                             增加 JobClass（JOB种类）查询条件信息。
 * </summary>
 *-----------------------------------------------------------------------
 */
namespace Orid.Management.Statistics.Service.OPUnitService.CommonQuery
{
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;
    using Orid.Management.Statistics.Service.OPUnitService.CommonQuery.ServiceModel;

    /// <summary>
    /// DataProvider
    /// 具体方法
    /// </summary>
    public class DataProvider
    {
        #region  Gets the sv unit info.
        /// <summary>
        /// Gets the  sv unit info.
        /// </summary>
        /// <param name="svID">The sv ID.</param>
        /// <param name="svName">Name of the sv.</param>
        /// <returns>返回 当前SV或SSV的所有组别信息</returns>
        public static List<SVUnitDataList> GetSelSvUnitInfo(string svID, string svName) 
        {
            List<SVUnitDataList> retSvUnitList = new List<SVUnitDataList>();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();

                StringBuilder sqlStringBuilder = new StringBuilder();

                sqlStringBuilder.Append(" SELECT DISTINCT [Unit]");
                sqlStringBuilder.Append(" FROM [SVInfo]");
                sqlStringBuilder.Append(" WHERE ( OpID=@svID OR OpName=@svName)");
                sqlStringBuilder.Append(" ORDER BY unit");

                #region SqlCommand
                
                using (SqlCommand cmd = new SqlCommand(sqlStringBuilder.ToString(), conn))
                {
                    cmd.Parameters.Add(new SqlParameter("@svID", SqlDbType.NVarChar, 50));
                    cmd.Parameters["@svID"].Value = svID;

                    cmd.Parameters.Add(new SqlParameter("@svName", SqlDbType.NVarChar, 50));
                    cmd.Parameters["@svName"].Value = svName;

                    SqlDataReader reaccount = cmd.ExecuteReader();
                  

                    while (reaccount.Read())
                    {
                        retSvUnitList.Add(new SVUnitDataList
                        {
                            Unit = Common.MyIsDBNull(reaccount, "Unit")
                        });
                    }

                    reaccount.Close();
                    conn.Close();
                }

                #endregion
              

                return retSvUnitList;
            }
        }
        #endregion  Gets the sv unit info.

        #region 页面上的控件的枚举值
        /// <summary>
        /// 页面上的控件的枚举值
        /// WCFOPEntrySpeedQuery服务
        /// </summary>
        /// <returns>返回 TagString 的 LIST 形式</returns>
        public static List<TagString> GetTagInfor()
        {
            List<TagString> ts = new List<TagString>();

            ts.Add(TagString.Handwriting);
            ts.Add(TagString.Printing);

            ts.Add(TagString.Japanese);
            ts.Add(TagString.EnglishAndNumber);
            ts.Add(TagString.Other);

            ts.Add(TagString.Trimer);
            ts.Add(TagString.α);
            ts.Add(TagString.β);
            ts.Add(TagString.A);
            ts.Add(TagString.B);
            ts.Add(TagString.D);
            ts.Add(TagString.E);
            ts.Add(TagString.手動仕分);
            // 01/10/2011 增加 F和GJOB的枚举值
            ts.Add(TagString.F);
            ts.Add(TagString.G);
            // 04/22/2011 增加 JOB种类 判定A，判定B的枚举值
            ts.Add(TagString.判定A);
            ts.Add(TagString.判定B);

            return ts;
        }

        #endregion 页面上的控件的枚举值

        #region 当前SV或SSV的所有OP信息
        /// <summary>
        /// 当前SV或SSV的所有OP 信息
        /// WCFOPEntrySpeedQuery服务
        /// </summary>
        /// <param name="svID">svID sv的ID号</param>
        /// <param name="opIdorName">opIdorName OP的姓名或ID</param>
        /// <returns>返回 SelectOPInforDataList 的 LIST 形式</returns>
        public static List<SelectOPInforDataList> GetSelectSVOPInfor(string svID, string opIdorName)
        {
            List<SelectOPInforDataList> selOPInforList = new List<SelectOPInforDataList>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();

                string sqlStr = "SELECT svi.OpID SVID,opi.[OpID],opi.[OpName],opi.[Unit]" +
                                " FROM [SVInfo] svi LEFT join [View_OperatorIDInfo] opi ON opi.Unit in(svi.Unit)" +
                                " WHERE svi.OpID=@svID" +
                                " AND ( opi.OpID LIKE @OpIdorName OR opi.OpName = @OpIdorName_1)";

                #region "SqlCommand"
                using (SqlCommand cmd = new SqlCommand(sqlStr, conn))
                {
                    cmd.Parameters.Add(new SqlParameter("@svID", svID));
                    cmd.Parameters.AddWithValue("@OpIdorName", "%" + opIdorName + "%");

                    cmd.Parameters.Add(new SqlParameter("@OpIdorName_1", SqlDbType.NVarChar, 50));
                    cmd.Parameters["@OpIdorName_1"].Value = opIdorName;


                    SqlDataReader reaccount = cmd.ExecuteReader();

                    while (reaccount.Read())
                    {
                        selOPInforList.Add(new SelectOPInforDataList()
                        {
                            SvID = Common.MyIsDBNull(reaccount, "SVID"),
                            OpID = Common.MyIsDBNull(reaccount, "OpID"),
                            OpName = Common.MyIsDBNull(reaccount, "OpName"),
                            Unit = Common.MyIsDBNull(reaccount, "Unit")
                        });
                    }

                    reaccount.Close();
                    conn.Close();
                }

                return selOPInforList;
                #endregion
            }
        }

        #endregion  当前SV或SSV的所有OP信息

        #region 线路查询信息
        /// <summary>
        /// 线路查询信息
        /// </summary>
        /// <returns></returns>
        public static List<LineData> GetLineInfor()
        {
            List<LineData> selLineInforList = new List<LineData>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();

                string sqlStr = "SELECT [Name] [LineName],[Value] [LineValue] FROM [View_MaServers] ORDER BY [LineValue]";

                #region "SqlCommand"
                using (SqlCommand cmd = new SqlCommand(sqlStr, conn))
                {
                    SqlDataReader reaccount = cmd.ExecuteReader();

                    while (reaccount.Read())
                    {
                        selLineInforList.Add(new LineData()
                        {
                            LineName = Common.MyIsDBNull(reaccount, "LineName"),
                            LineValue = Common.MyIsDBNull(reaccount, "LineValue"),
                        });
                    }

                    reaccount.Close();
                    conn.Close();
                }

                return selLineInforList;
                #endregion
            }
        }
        #endregion 线路查询信息

        #region 案件名查询信息
        /// <summary>
        /// 案件名查询信息 v2.13
        /// </summary>
        /// <returns></returns>
        /// <value>
        /// 04/25/2011 Add
        /// 06/17/2011 EDIT 案件名查询
        /// </value>
        public static List<LineData> GetMatterNameInfor()
        {
            // 用LineData类返回数据
            List<LineData> selMatterNameInforList = new List<LineData>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();

                string sqlStr = "SELECT [MatterID],[Value],[Name] FROM [View_MatterName]";

                #region "SqlCommand"
                using (SqlCommand cmd = new SqlCommand(sqlStr, conn))
                {
                    SqlDataReader reaccount = cmd.ExecuteReader();

                    while (reaccount.Read())
                    {
                        /*
                        MatterID	Value	     Name
                            1	  KYBER NOTE	KYBER
                            2	  手書き	    Handwriting
                         * SELECT  Matters.MatterID,Dict.Value, Dict.Name
                         * FROM   Dict INNER JOIN
                         * Matters ON Dict.Value = Matters.MatterName
                         *  WHERE  (Dict.Type = 'MatterName')
                         *  06/29/2011 以上不用
                        */

                        /*
                        MatterID	Value	      Name
                        0	        仕分け	      仕分け
                        1	        KYBER NOTE	  KYBER NOTE
                        2	        手書き	      手書き
                        SELECT     MatterID, MatterName AS Value, MatterName AS Name
                        FROM         dbo.Matters
                        */
                        selMatterNameInforList.Add(new LineData()
                        {
                            LineID =Common.MyIsDBNull(reaccount, "MatterID"),// 显示值
                            LineValue = Common.MyIsDBNull(reaccount, "Value"),// 显示值
                            LineName = Common.MyIsDBNull(reaccount, "Name") // 查询值
                            
                        });
                    }

                    reaccount.Close();
                    conn.Close();
                }

                return selMatterNameInforList;
                #endregion
            }
        }
        #endregion 案件名查询信息

        #region UserOrder（客户种类）查询信息
        /// <summary>
        /// UserOrder（客户种类）查询信息 v2.13
        /// </summary>
        /// <returns></returns>
        /// <value>
        /// 06/29/2011 Add
        /// </value>
        public static List<LineData> GetUserOrderInfor()
        {
            // 用LineData类返回数据
            List<LineData> selUserOrderInforList = new List<LineData>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();

                string sqlStr = "SELECT [Name],[Value] FROM Dict WHERE Type ='UserOrder'AND (Value IS NOT NULL) AND (Value NOT IN ('Null', '')) ";

                #region "SqlCommand"
                using (SqlCommand cmd = new SqlCommand(sqlStr, conn))
                {
                    SqlDataReader reaccount = cmd.ExecuteReader();

                    while (reaccount.Read())
                    {
                        selUserOrderInforList.Add(new LineData()
                        {
                            LineName = Common.MyIsDBNull(reaccount, "Name"),
                            LineValue = Common.MyIsDBNull(reaccount, "Value"),
                        });
                    }

                    reaccount.Close();
                    conn.Close();
                }

                return selUserOrderInforList;
                #endregion
            }
        }
        #endregion UserOrder（客户种类）查询信息

        #region ScriptType(帐票种类) 查询信息
        /// <summary>
        /// ScriptType(帐票种类) 查询信息 v2.14
        /// </summary>
        /// <returns></returns>
        /// <value>
        /// 07/04/2011 Add
        /// </value>
        public static List<LineData> GetScriptTypeInfor()
        {
            List<LineData> selScripTypeList = new List<LineData>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();
                string sqlStr = "SELECT [Name],[Value],[Type] FROM [View_ScriptType]";

                #region SqlCommand
                using (SqlCommand cmd = new SqlCommand(sqlStr, conn))
                {
                    SqlDataReader read = cmd.ExecuteReader();
                    while (read.Read())
                    {
                        ///Name	        Value	    Type
                        ///Handwriting	手書	    ScriptType
                        ///Printing	    活字	    ScriptType
                        ///
                        ///SELECT     Name, Value, Type
                        ///FROM dbo.Dict
                        ///WHERE (Type = 'ScriptType') AND (Value IS NOT NULL) AND (Value NOT IN ('Null', ''))

                        selScripTypeList.Add(new LineData()
                        {
                            LineName = Common.MyIsDBNull(read, "Name"),
                            LineValue = Common.MyIsDBNull(read, "Value"),
                        });
                    }

                    read.Close();
                    conn.Close();
                }

                return selScripTypeList;
                #endregion SqlCommand
            }

        }
        #endregion ScriptType(帐票种类) 查询信息
        

        #region WordType（文字种类）查询条件信息。
        /// <summary>
        /// WordType（文字种类）查询条件信息。 v2.15
        /// </summary>
        /// <returns></returns>
        /// <value>
        /// 07/05/2011 Add
        /// </value>
        public static List<LineData> GetWordTypeInfor()
        {
            List<LineData> selWordTypeList = new List<LineData>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();
                string sqlStr = "SELECT [Name],[Value],[Type] FROM [View_WordType]";

                #region SqlCommand
                using (SqlCommand cmd = new SqlCommand(sqlStr, conn))
                {
                    SqlDataReader read = cmd.ExecuteReader();
                    while (read.Read())
                    {
                        ///Name	            Value	        Type
                        ///EnglishAndNumber	英数字	    WordType
                        ///Japanese	        日本語	    WordType
                        ///other	        其の他	    WordType
                        ///Chinese	        中国語	    WordType
                        ///
                        ///SELECT     Name, Value, Type
                        ///FROM dbo.Dict
                        ///WHERE (Type = 'WordType') AND (Value IS NOT NULL) AND (Value NOT IN ('Null', ''))

                        selWordTypeList.Add(new LineData()
                        {
                            LineName = Common.MyIsDBNull(read, "Name"),
                            LineValue = Common.MyIsDBNull(read, "Value"),
                        });
                    }

                    read.Close();
                    conn.Close();
                }

                return selWordTypeList;
                #endregion SqlCommand
            }

        }
        #endregion WordType（文字种类）查询条件信息。

        #region JobClass（JOB种类）查询条件信息。
        /// <summary>
        /// JobClass（JOB种类）查询条件信息。 v2.15
        /// </summary>
        /// <returns></returns>
        /// <value>
        /// 07/05/2011 Add
        /// </value>
        public static List<LineData> GetJobClassInfor()
        {
            List<LineData> selWordTypeList = new List<LineData>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();
                string sqlStr = "SELECT [Name],[Value],[Type] FROM [View_JobClass]";

                #region SqlCommand
                using (SqlCommand cmd = new SqlCommand(sqlStr, conn))
                {
                    SqlDataReader read = cmd.ExecuteReader();
                    while (read.Read())
                    {
                        
                        /// Name	Value	Type
                        /// A	    A	    JobClass
                        /// B	    B	    JobClass
                        /// D	    D	    JobClass
                        /// 
                        ///SELECT     Name, Value, Type
                        ///FROM dbo.Dict
                        ///WHERE (Type = 'JobClass') AND (Value IS NOT NULL) AND (Value NOT IN ('Null', ''))

                        selWordTypeList.Add(new LineData()
                        {
                            LineName = Common.MyIsDBNull(read, "Name"),
                            LineValue = Common.MyIsDBNull(read, "Value"),
                        });
                    }

                    read.Close();
                    conn.Close();
                }

                return selWordTypeList;
                #endregion SqlCommand
            }

        }
        #endregion JobClass（JOB种类）查询条件信息。
    }
}
